<?php
/* ******************************
 *	letterA aJax actions
 * ******************************
 */

include('../../includes/classes/core.php');

$action		= $_REQUEST['act'];
$error		= '';
$data		= '';
$user_id	= $_SESSION['USERID'];
$sum        = '';

switch ($action) {
    case 'get_edit_page':
	    $letter_id	= $_REQUEST['id'];
		$page		= GetPage( GetAletterInfo($letter_id) );

        $data 		= array('page'	=> $page);

        break;
    case 'get_list':
	    $count = $_REQUEST['count'];
	    $hidden = $_REQUEST['hidden'];

	    $rResult = mysql_query("SELECT 	`d_letter`.`id`,
                        				`d_letter`.fact_end_date,
                        				 CONCAT( '№: ე-',`d_letter`.`id`),
                        				 CONCAT(client.`name`, '(', client_objects.`name`, ')'),
                        				 SUM(IF(warehouse_detail.type = 2, 0, e_letter_details.price)),
                        				 CASE	WHEN e_letter_status != 0 THEN	'კი'
                        						WHEN e_letter_status  = 0 THEN	'არა'
                        				 END AS `signature`,
	                                      invoice_id 
                        FROM    d_letter
                        JOIN    e_letter_details 	ON 	e_letter_details.d_letter_id=d_letter.id
                        JOIN    warehouse_detail 	ON 	warehouse_detail.id=e_letter_details.warehouse_detail_id and warehouse_detail.actived = 1
                        JOIN    client_objects 		ON 	client_objects.id = d_letter.client_object_id
                        JOIN    client 				ON 	client.id = client_objects.client_id
                        WHERE   d_letter.`status` >= 4 and d_letter.actived = 1 $_REQUEST[filter]
                        GROUP BY 	d_letter.id");

		$data = array(
			"aaData"	=> array()
		);

		while ( $aRow = mysql_fetch_array( $rResult ) )
		{
			$row = array();
			for ( $i = 0 ; $i < $count ; $i++ )
			{
				/* General output */
				$row[] = $aRow[$i];
			}
			$data['aaData'][] = $row;
		}

        break;
    case 'save_eletter_status' :
    	$dltter_id     = $_REQUEST['id'];
    	$requester     = $_REQUEST['req'];
    	$new_requester = $_REQUEST['nreq'];
    	$client_obj    = $_REQUEST['cobj'];
    	$responsible_person = $_REQUEST['rp'];
    	$car_id 			= $_REQUEST['ci'];
    	
    	
    	Saveeletter($user_id, $dltter_id, $requester, $new_requester, $client_obj,$responsible_person,$car_id);

    	break;
    case 'get_overhead_id':
        $letter_id		= $_REQUEST['id'];
     if(mysql_num_rows(mysql_query("SELECT e_letter_signature FROM `d_letter` WHERE id=$letter_id AND NOT ISNULL(e_letter_signature)"))>0) {   
    	$overhead_id	= GetOverheadID($letter_id);
    	ChangeLocation($letter_id);
    	$data = array('overhead_id' => $overhead_id);
       }
       else{   $error = 'შემოიტანეთ ხელმომწერი';	}
    	break;
    case 'make_sale':
    	$dltter_id     = $_REQUEST['id'];
    	MakeSale($user_id, $dltter_id);

    	break;
    default:
       $error = 'Action is Null';
}

$data['error'] = $error;
echo json_encode($data);

function GetAletterInfo($letter_id){
	$res = mysql_fetch_assoc(mysql_query("SELECT	`d_letter`.`id` as `id`,
													`d_letter`.`start_date` as `date`,
	                                                 transporter_company_id,
													CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') AS `client`,
													`d_letter`.`comment` as `coment`
									FROM			`d_letter`
									LEFT JOIN		`client_objects` ON `d_letter`.`client_object_id` = `client_objects`.`id`
									LEFT JOIN		`client` ON `client`.`id` = `client_objects`.`client_id`
									WHERE			`d_letter`.`id` = '$letter_id'"));
	return $res;
}

function GetOverheadID($letter_id){
	$res = mysql_fetch_assoc(mysql_query("	SELECT     `e_letter_overhead_id`
											FROM       `d_letter`
											WHERE      `id` = '$letter_id'"));
	return $res['e_letter_overhead_id'];
}
function ChangeLocation($letter_id){
	$csql = mysql_query("	SELECT		warehouse_detail.barcode as `barcode`,
			warehouse.client_object_id  as `location`
			FROM		a_letter
			LEFT JOIN	warehouse ON warehouse.a_letter_id = a_letter.id
			LEFT JOIN	warehouse_detail ON warehouse_detail.warehouse_id = warehouse.id
			WHERE		a_letter.id = '$letter_id'");

	while ( $aRow = mysql_fetch_array( $csql ) ){
		mysql_query("	UPDATE 	`reserve`
		SET 	`reserve`.`object_id` = '$aRow[location]'
		WHERE	`reserve`.`barcode` = '$aRow[barcode]' ");
	}
}



function MakeSale($user_id, $dltter_id){
	$c_date = date('Y-m-d H:i:s');
	mysql_query("INSERT INTO sale_transaction
					(user_id, object_id, start_date, end_date, status)
				VALUES
					($user_id, 131, '$c_date', '$c_date', 11)");
	$transaction_id = mysql_insert_id();
	$sumcost		= 0.0;
	$rResult= mysql_query("	SELECT		`warehouse_producing`.`id` AS `warehouse_producing_id`,
										`warehouse_producing`.`production_id` as `production_id`,
										`printer_identity`.`amount` as `amount`,
										(SELECT ROUND(`reserve`.`price`, 2) FROM	reserve WHERE	`reserve`.`production_id` = `warehouse_producing`.`production_id` ORDER BY	reserve.id	DESC LIMIT 1) as `price`
							FROM		`warehouse`
							LEFT JOIN	`warehouse_detail` ON `warehouse_detail`.`warehouse_id` = `warehouse`.`id`
							LEFT JOIN	`warehouse_producing` ON `warehouse_producing`.`warehouse_detail_id` = `warehouse_detail`.`id`
							LEFT JOIN	`client_objects` ON `client_objects`.`id` = `warehouse`.`client_object_id`
							LEFT JOIN	`client` ON `client`.`id` = `client_objects`.`client_id`
							LEFT JOIN	`printer_identity` ON `printer_identity`.`production_id` = `warehouse_detail`.`prod_id` AND `printer_identity`.`parts_type` = `warehouse_producing`.`printer_part_id`
							WHERE  		`warehouse`.`d_letter_id` = '$dltter_id'");

	while ( $aRow = mysql_fetch_array( $rResult ) )
	{
		mysql_query("INSERT INTO sale_transaction_detail
						(user_id, transaction_id, warehouse_producing_id, production_id, product_quantity, self_cost, cost, cost_type)
					VALUES
						($user_id, $transaction_id, $aRow[warehouse_producing_id], $aRow[production_id], $aRow[amount], $aRow[price], $aRow[price], 1)");
		$sumcost = (float)$sumcost + (float)$aRow[price] * (float)$aRow[amount];
	}
	mysql_query("	UPDATE sale_transaction
					SET		sum_self_cost = $sumcost,
							sum_cost    	 = $sumcost
					WHERE id = '$transaction_id'");
}

function Saveeletter($user_id, $dltter_id, $requester, $new_requester, $client_obj,$responsible_person,$car_id){
	if(!empty($new_requester)){
			mysql_query("INSERT INTO `client_object_persons`
							(`user_id`, `client_object_id`, `name`)
						VALUES
							($user_id, $client_obj, '$new_requester')");

			$requester = mysql_insert_id();
	}
	$code='';
	if(!empty($requester)){
	    $code="`e_letter_status` = 1,
	            e_letter_signature = '$requester',
	            e_letter_signature_date = NOW(),";
	     
	}
	mysql_query("UPDATE
						`d_letter`
					SET
					    $code
						receiver1         ='$responsible_person',
						car_id            = '$car_id'	
					WHERE
						`id` = '$dltter_id'");
}

function GetClientPersons($client_objects_id, $point){
	$data = '';	
	$req = mysql_query("SELECT		`client_object_persons`.`id`,
									`client_object_persons`.`name`
						FROM		`client_objects`
						LEFT JOIN	`client_object_persons` ON `client_objects`.`id` = `client_object_persons`.`client_object_id`
						LEFT JOIN	`client` ON `client`.`id` = `client_objects`.`client_id`
						WHERE		`client_objects`.`id` = '$client_objects_id'");
	
		$data .= '<option value="0" selected="selected">&nbsp;</option>';
	

	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $point){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}
	return $data;
}

function addNull($sum){
	if($sum != '' && $sum != '0'){
		$parts = explode('.', $sum);
		$sum1 ='';
		$sum1.= $parts[0];
		if( $parts[1] == ''){
			$sum1.= '.00';
		}else{
			$sum1 = $sum;
		}
		return $sum1;
	}else{
		return '0.00';
	}
}

function print1($dletter_id){
	$rsql = mysql_query("SELECT 	printer_parts_type.`name`,
				production.`name`,
				barcodes.serial_number,
				IF(warehouse_detail.type = 2, 0, e_letter_details.price)
FROM  warehouse_detail
JOIN	e_letter_details 		ON warehouse_detail.id	= e_letter_details.warehouse_detail_id
JOIN	production 					ON production.id = warehouse_detail.prod_id
JOIN	printer_parts_type 	ON printer_parts_type.id = e_letter_details.part_type_id
JOIN  barcodes 						ON warehouse_detail.barcode = barcodes.`code`   
WHERE	e_letter_details.d_letter_id = '$dletter_id' AND warehouse_detail.actived=1");
	GLOBAL $sum;
	$data = '';
	while ( $aRow = mysql_fetch_array( $rsql ) ){
					$data .= '<tr>';
					for ( $i = 0 ; $i < 4 ; $i++ ){
						$data .='<td  style="border:1px solid black !important; vertical-align: middle !important; text-align: center;">'.$aRow[$i].'</td>';
						if( $i == 3){
							$sum = floatval($sum) + floatval($aRow[$i]);
						}
					}
					$data .= '</tr>';
	}
	return $data;
}
function GetPersons($person_id)
{
	$data = '';
	$req = mysql_query("SELECT `id`, `name`
						FROM `persons`
						WHERE `persons`.`position` !=13 && `persons`.actived != 0");

	$data .= '<option value="' . 0 . '" selected="selected">' . '' . '</option>';

	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $person_id){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}
	return $data;
}
function GetAutoNumber($point)
{
	$data = '';
	$req = mysql_query("SELECT	`id`, `name`
						FROM	`object`
						WHERE	`type` = 3 && `object`.actived != 0"); // <<<<---------------------Type!!!
	$data .= '<option value="' . 0 . '" selected="selected">' . '' . '</option>';
	while( $res = mysql_fetch_assoc($req)){
		if($res['id'] == $point){
			$data .= '<option value="' . $res['id'] . '" selected="selected">' . $res['name'] . '</option>';
		} else {
			$data .= '<option value="' . $res['id'] . '">' . $res['name'] . '</option>';
		}
	}

	return $data;
}
function GetPage($res = ''){
	GLOBAL $sum;
	$reQ = mysql_fetch_assoc(mysql_query("SELECT		`d_letter`.`id` as `id`,
														`d_letter`.`job_end_date` as `date`,
														CONCAT(`client`.`name`,'(',`client_objects`.`name`, ')') AS `client`,
														`client_objects`.`id` as `obj_id`,
	                                                    rs_wdsl_status.end_date,  
														`d_letter`.`e_letter_signature` as `e_letter_signature`,
														`d_letter`.`e_letter_overhead_id` as `overhead_id`,
	                                                    `d_letter`.car_id,
	                                                    `d_letter`.receiver1,
														d_letter.`status`
										FROM			`d_letter`
										LEFT JOIN		`client_objects` ON `d_letter`.`client_object_id` = `client_objects`.`id`
										LEFT JOIN		`client` ON `client`.`id` = `client_objects`.`client_id`
	                                    LEFT JOIN    rs_wdsl_status ON rs_wdsl_status.id=d_letter.e_letter_overhead_id
										WHERE			`d_letter`.`id` = '$res[id]'"));

	$data = '
		<div id="dialog-form">
				<fieldset>
					<legend>ძირითადი ინფორმაცია</legend>
					<table width="80%" class="dialog-form-table" cellpadding="10px" >
						<tr align="center">
							<th colspan="2">
								<label >ე ბარათი №<span style="border-bottom: 1px solid #000; padding: 0 30px; margin-left: 50px;">' . $reQ['id']. '</span></label>
							</th>
						</tr>
						<tr align="center">
							<th colspan="2">
								<label >დასრულების თარიღი<span style="border-bottom: 1px solid #000; padding: 0 30px; margin-left: 50px;">' . $reQ['date']. '</span></label>
							</th>
						</tr>
						<tr align="center">
							<th colspan="2">
								<label >კლიენტი<span style="border-bottom: 1px solid #000; padding: 0 30px; margin-left: 50px;">' . $reQ['client']. '</span></label>
							</th>
						</tr>
					</table>
				</fieldset>
				<div style="padding: 2px;"></div>
				<fieldset>
					<table style="width: 550px !important; border:1px solid black !important;" cellpadding="0" cellspacing="0">
						<tr style="background-color: black !important; border:1px solid black !important; color: white !important;">
							<td style="width: 45% !important; font-size: 9pt; border:1px solid black !important; vertical-align: middle !important; text-align: center;"><span>სერვისი</span></td>
							<td style="width: 25% !important; font-size: 9pt; border:1px solid black !important; vertical-align: middle !important; text-align: center;"><span>მოდელი</span></td>
							<td style="width: 20% !important; font-size: 9pt; border:1px solid black !important; vertical-align: middle !important; text-align: center;">შტრიხკოდი</td>
							<td style="width: 10% !important; font-size: 9pt; border:1px solid black !important; vertical-align: middle !important; text-align: center;">ფასი</td>
						</tr>';

	$data .= print1($res['id']);



	$data .= '
					</table>
				<table>
						<div style="text-align: right; font-size: 10pt; margin-top: 4px;">
							<p>თანხა სულ :&nbsp;<span style="border-bottom: 1px solid #000; padding: 0 30px; margin-left: 0px;">' . addNull($sum) . '&nbsp;&nbsp;ლარი</span></p>
						</div>
					</table>
				</fieldset>
				<fieldset style="margin-top: 4px;">
					<table width="100%" class="dialog-form-table" cellpadding="10px" >
					<tr>
						<th></th>	    
						<th><label for="responsible_person">მძღოლი</label></th>
						<th><label for="car_id">ა/მ ნომერი</label></th>
					</tr>
					<tr>
						<th></th>	   	    
						<th>
							<select id="responsible_person" class="idls address">'. GetPersons($reQ[receiver1]) .'</select>
						</th>
						<th>
							<select id="car_id" class="idls user_id">'. GetAutoNumber($reQ[car_id]) .'</select>
						</th>
					</tr>
						<tr>
							<th><label for="new_client_person"></label></th>
							<th><label for="requester">ხელმომწერელი პიროვნება</label></th>
							<th><label for="ok_part">&nbsp;</label></th>
						</tr>
						<tr>
							<td>
								<input type="checkbox" class="new_requester" class="idls" />
							</td>
							<td>
								<input id="new_requester" type="text" class="idle" onblur="this.className=\'idle\'" onfocus="this.className=\'activeField\'" style="display: none; width: 219px;" />
								<select id="requester" class="idls address">' . GetClientPersons($reQ['obj_id'], $reQ['e_letter_signature'] ) . '</select>
							</td>
							<td>
								<button type="button" id="ok_part" style="outline:none; margin-right: 140px;"class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only">
									<span class="ui-button-text">შენახვა</span>
								</button>
							</td>
						</tr>
					</table>
				</fieldset>
	    </div>
	<div class="inner-table" >
		<input type="hidden" id="d_letter_id" value="' . $res['id'] . '" />
		<input type="hidden" id="client_object_id" value="' . $reQ['obj_id'] . '" />
		<input type="hidden" id="overhead_id" value="' . $reQ['overhead_id'] . '" />
		<input type="hidden" id="client_ob_trans"  value="' . $res[transporter_company_id]. '" />  		    
		<input type="hidden" id="rs_end_date" value="' . $reQ['end_date'] . '" />

	</div>';

	return $data;
}

?>